#!/usr/bin/python3

import os
import subprocess
import pymysql
from pathlib import Path

##result module define

#result info
class PtResultInfo:
    total_time_name='total_time'
    total_transactions_name='total_transactions'
    total_queries_name='total_queries'
    tps_name='tps'
    qps_name='qps'
    errors_name='errors'
    error_rate_name='error_rate'
    latency_min_name='latency_min'
    latency_avg_name='latency_avg'
    latency_max_name='latency_max'
    reconnects_name='reconnects'
    percentile_name='percentile'
    total_time=''
    total_transactions=''
    total_queries=''
    tps=''
    qps=''
    errors=''
    error_rate=''
    latency_min=''
    latency_avg=''
    latency_max=''
    reconnects=''
    percentile=''
    def parse(self, result_file_name):

        result_file = Path(result_file_name)
        if not result_file.is_file():
            print("result_file=" + result_file_name + "does not exist or not a file!")
            return

        total_time_command = "grep 'execution time (avg/stddev):' " + result_file_name + " | awk 'NR==1'"
        total_time_info = subprocess.getoutput(total_time_command)

        total_time_info_arr = total_time_info.split(':')
        total_time_info_arr_len = len(total_time_info_arr)
        if (total_time_info_arr_len > 1):
            total_time_info = total_time_info_arr[1]
            total_time_info_arr = total_time_info.split('/')
            total_time_info_arr_len = len(total_time_info_arr)
            if (total_time_info_arr_len > 0):
                total_time = total_time_info_arr[0]
                self.total_time = total_time.strip()
            else:
                print("invalid total_time_info=", total_time_info)
        else:
            print("invalid total_time_info=", total_time_info)

        transactions_command="grep 'transactions:' " + result_file_name + " | awk 'NR==1'"
        transactions_info=subprocess.getoutput(transactions_command)
        transactions_info_arr=transactions_info.split(':')
        transactions_info_arr_len=len(transactions_info_arr)
        if(transactions_info_arr_len > 1):
            transactions_info=transactions_info_arr[1]
            transactions_info_arr = transactions_info.split('(')
            transactions_info_arr_len = len(transactions_info_arr)
            if (transactions_info_arr_len > 1):
                total_transactions= transactions_info_arr[0]
                self.total_transactions= total_transactions.strip()
                transactions_info= transactions_info_arr[1]
                transactions_info_arr = transactions_info.split('per')
                transactions_info_arr_len = len(transactions_info_arr)
                if (transactions_info_arr_len > 0):
                    tps = transactions_info_arr[0]
                    self.tps = tps.strip()
                else:
                    print("invalid transactions_info=", transactions_info)
            else:
                print("invalid transactions_info=", transactions_info)
        else:
            print("invalid transactions_info=", transactions_info)


        queries_command="grep 'queries:' " + result_file_name + " | awk 'NR==1'"
        queries_info=subprocess.getoutput(queries_command)
        queries_info_arr=queries_info.split(':')
        queries_info_arr_len=len(queries_info_arr)
        if(queries_info_arr_len > 1):
            queries_info=queries_info_arr[1]
            queries_info_arr = queries_info.split('(')
            queries_info_arr_len = len(queries_info_arr)
            if (queries_info_arr_len > 1):
                total_queries= queries_info_arr[0]
                self.total_queries= total_queries.strip()
                queries_info= queries_info_arr[1]
                queries_info_arr = queries_info.split('per')
                queries_info_arr_len = len(queries_info_arr)
                if (queries_info_arr_len > 0):
                    qps = queries_info_arr[0]
                    self.qps = qps.strip()
                else:
                    print("invalid queries_info=", queries_info)
            else:
                print("invalid queries_info=", queries_info)
        else:
            print("invalid queries_info=", queries_info)

        errors_command="grep 'ignored errors:' " + result_file_name + " | awk 'NR==1'"
        errors_info=subprocess.getoutput(errors_command)
        errors_info_arr=errors_info.split(':')
        errors_info_arr_len=len(errors_info_arr)
        if(errors_info_arr_len > 1):
            errors_info=errors_info_arr[1]
            errors_info_arr = errors_info.split('(')
            errors_info_arr_len = len(errors_info_arr)
            if (errors_info_arr_len > 1):
                errors= errors_info_arr[0]
                self.errors= errors.strip()
                errors_info= errors_info_arr[1]
                errors_info_arr = errors_info.split('per')
                errors_info_arr_len = len(errors_info_arr)
                if (errors_info_arr_len > 0):
                    error_rate = errors_info_arr[0]
                    self.error_rate = error_rate.strip()
                else:
                    print("invalid errors_info=", queries_info)
            else:
                print("invalid errors_info=", errors_info)
        else:
            print("invalid errors_info=", errors_info)

        latency_min_command="grep 'min:' " + result_file_name + " | awk 'NR==1'"
        latency_min_info=subprocess.getoutput(latency_min_command)
        latency_min_info_arr=latency_min_info.split(':')
        latency_min_info_arr_len=len(latency_min_info_arr)
        if(latency_min_info_arr_len > 1):
            latency_min_info=latency_min_info_arr[1]
            self.latency_min= latency_min_info.strip()
        else:
            print("invalid latency_min_info=", latency_min_info)

        latency_avg_command="grep 'avg:' " + result_file_name + " | awk 'NR==1'"
        latency_avg_info=subprocess.getoutput(latency_avg_command)
        latency_avg_info_arr=latency_avg_info.split(':')
        latency_avg_info_arr_len=len(latency_avg_info_arr)
        if(latency_avg_info_arr_len > 1):
            latency_avg_info=latency_avg_info_arr[1]
            self.latency_avg= latency_avg_info.strip()
        else:
            print("invalid latency_avg_info=", latency_avg_info)

        latency_max_command="grep 'max:' " + result_file_name + " | awk 'NR==1'"
        latency_max_info=subprocess.getoutput(latency_max_command)
        latency_max_info_arr=latency_max_info.split(':')
        latency_max_info_arr_len=len(latency_max_info_arr)
        if(latency_max_info_arr_len > 1):
            latency_max_info=latency_max_info_arr[1]
            self.latency_max= latency_max_info.strip()
        else:
            print("invalid latency_max_info=", latency_max_info)

        reconnects_command="grep 'reconnects:' " + result_file_name + " | awk 'NR==1'"
        reconnects_info=subprocess.getoutput(reconnects_command)
        reconnects_info_arr=reconnects_info.split(':')
        reconnects_info_arr_len=len(reconnects_info_arr)
        if(reconnects_info_arr_len > 1):
            reconnects_info=reconnects_info_arr[1]
            reconnects_info_arr = reconnects_info.split('(')
            reconnects_info_arr_len = len(reconnects_info_arr)
            if (reconnects_info_arr_len > 0):
                reconnects= reconnects_info_arr[0]
                self.reconnects= reconnects.strip()
            else:
                print("invalid reconnects_info=", reconnects_info)
        else:
            print("invalid reconnects_info=", reconnects_info)

        percentile_command="grep 'percentile:' " + result_file_name + " | awk 'NR==1'"
        percentile_info=subprocess.getoutput(percentile_command)
        percentile_info_arr=percentile_info.split(':')
        percentile_info_arr_len=len(percentile_info_arr)
        if(percentile_info_arr_len > 1):
            percentile_info=percentile_info_arr[1]
            self.percentile= percentile_info.strip()
        else:
            print("invalid percentile_info=", percentile_info)
    def print_result(self):
        print("total_time=%s,"
              "total_transactions=%s,"
              "total_queries=%s,"
              "tps=%s,qps=%s,"
              "errors=%s,"
              "error_rate=%s,"
              "latency_min=%s,"
              "latency_avg=%s,"
              "latency_max=%s,"
              "reconnects=%s,"
              "percentile=%s"
              %(self.total_time,
                self.total_transactions,
                self.total_queries,
                self.tps,
                self.qps,
                self.errors,
                self.error_rate,
                self.latency_min,
                self.latency_avg,
                self.latency_max,
                self.reconnects,
                self.percentile))


###env
##connect info
mtsql_host = os.getenv('MTSQL_METDATA_HOST')
mtsql_port_str = os.getenv('MTSQL_METDATA_PORT')
mtsql_port = int(mtsql_port_str)
mtsql_user = os.getenv('MTSQL_METDATA_USER')
mtsql_password = os.getenv('MTSQL_METDATA_PASSWORD')
mtsql_database = os.getenv('MTSQL_METDATA_DATABASE')
mtsql_charset = os.getenv('MTSQL_METDATA_CHARSET')

##result info
#
mtsql_pt_feature = os.getenv('MTSQL_PT_FEATURE')
mtsql_pt_with_feature = os.getenv('MTSQL_PT_WITH_FEATURE')
mtsql_pt_test_scenarios = os.getenv('MTSQL_PT_TEST_SCENARIOS')
mtsql_pt_sub_test_scenarios = os.getenv('MTSQL_PT_SUB_TEST_SCENARIOS')
mtsql_pt_db_type = os.getenv('MTSQL_PT_DB_TYPE')
mtsql_pt_test_case = os.getenv('MTSQL_PT_TEST_CASE')
mtsql_pt_test_batch_no = os.getenv('MTSQL_PT_TEST_BATCH_NO')
mtsql_pt_os_name = os.getenv('MTSQL_PT_OS_NAME')
mtsql_pt_machine_hardware_name = os.getenv('MTSQL_PT_MACHINE_HARDWARE_NAME')
mtsql_pt_deploy_type = os.getenv('MTSQL_PT_DEPLOY_TYPE')
mtsql_pt_deploy_type = mtsql_pt_deploy_type.lower()
#mtsql_pt_threads = os.getenv('MTSQL_PT_TEST_SCENARIOS')

#
mtsql_pt_is_force_update = os.getenv('MTSQL_PT_IS_FORCE_UPDATE')
##result info
mtsql_pt_result_file_path=os.getenv('MTSQL_PT_RESULT_FILE_PATH')

mtsql_pt_test_threads = ['16', '32', '64', '128', '256', '512', '1024', '2048', '4096', '8192', '16384']
#mtsql_pt_test_threads = ['32']

#connect db
db = pymysql.connect(host=mtsql_host, port=mtsql_port, user=mtsql_user, password=mtsql_password,
                     database=mtsql_database, charset=mtsql_charset)

cursor = db.cursor()

for mtsql_pt_test_thread in mtsql_pt_test_threads:
    mtsql_pt_result_info= PtResultInfo()
    mtsql_pt_result_file_name=mtsql_pt_result_file_path \
                            + "/sysbench_run_result_" \
                            + mtsql_pt_test_case \
                            + "_" \
                            + mtsql_pt_test_thread \
                            + ".log"
    print("mtsql_pt_result_file_name=" + mtsql_pt_result_file_name)
    mtsql_pt_result_info.parse(mtsql_pt_result_file_name)
    mtsql_pt_result_info.print_result()

    insert_pt_result_info_param=((mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                                 mtsql_pt_test_thread, mtsql_pt_result_info.total_time_name, mtsql_pt_result_info.total_time),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.total_transactions_name, mtsql_pt_result_info.total_transactions),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name,mtsql_pt_deploy_type,
                             mtsql_pt_test_thread,mtsql_pt_result_info.tps_name, mtsql_pt_result_info.tps),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.total_queries_name, mtsql_pt_result_info.total_queries),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.qps_name, mtsql_pt_result_info.qps),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.errors_name, mtsql_pt_result_info.errors),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.error_rate_name, mtsql_pt_result_info.error_rate),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.latency_min_name, mtsql_pt_result_info.latency_min),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.latency_avg_name, mtsql_pt_result_info.latency_avg),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.latency_max_name, mtsql_pt_result_info.latency_max),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.reconnects_name, mtsql_pt_result_info.reconnects),
                            (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                             mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                             mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                             mtsql_pt_test_thread, mtsql_pt_result_info.percentile_name, mtsql_pt_result_info.percentile))

    inset_pt_result_info_sql= "insert ignore into pt_feature_result_info \
                                 (feature, with_feature, test_scenarios, sub_test_scenarios, db_type, test_case,  \
                                 test_batch_no, os_name, machine_hardware_name, deploy_type, threads, name, value) \
                                 values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

    delete_pt_result_info_sql = "DELETE FROM pt_feature_result_info " \
                                "WHERE feature = %s " \
                                "and with_feature = %s " \
                                "and test_scenarios = %s  " \
                                "and sub_test_scenarios = %s " \
                                "and db_type = %s  " \
                                "and test_case = %s " \
                                "and test_batch_no = %s " \
                                "and os_name = %s " \
                                "and machine_hardware_name = %s  " \
                                "and deploy_type = %s " \
                                "and threads = %s "

    delete_pt_result_info_param=(mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                                  mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                                  mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                                  mtsql_pt_test_thread)

    try:
        if mtsql_pt_is_force_update == '1' :
            cursor.execute(delete_pt_result_info_sql, delete_pt_result_info_param)
        cursor.executemany(inset_pt_result_info_sql, insert_pt_result_info_param)
        db.commit()
    except Exception as e:
        db.rollback()
        print("update pt feature result catch exception, system rollback the transaction!")
        print(e)

db.close()

